CREATE OR REPLACE FUNCTION check_product_availability()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_available_quantity INT;
    v_order_quantity INT;
BEGIN
    -- Get the quantity requested by the order
    SELECT quantity
    INTO v_order_quantity
    FROM "order"
    WHERE order_num = NEW.order_num;

    -- Get the currently available quantity of the product
    SELECT quantity
    INTO v_available_quantity
    FROM sells
    WHERE code = NEW.code;

    -- Check whether the product exists
    IF v_available_quantity IS NULL THEN
        RAISE EXCEPTION
            'Product % is not available in any store.',
            NEW.code;
    END IF;

    -- Check whether there is enough stock
    IF v_order_quantity > v_available_quantity THEN
        RAISE EXCEPTION
            'Insufficient stock for product %. Available: %, requested: %.',
            NEW.code,
            v_available_quantity,
            v_order_quantity;
    END IF;

    RETURN NEW;
END;
$$;


CREATE TRIGGER trg_check_product_availability
BEFORE INSERT OR UPDATE
ON includes
FOR EACH ROW
EXECUTE FUNCTION check_product_availability();